package com.zlj.jdbc.utils.transaction;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.zlj.jdbc.utils.Employee;

/*
 * 添加事务之后的测试
 */
public class TestJdbcUtils04 {
	
	/*
	 * 测试
	 */
	@Test
	public void test01() throws SQLException {
		//测试单笔查询
		/*Employee querySingle = querySingle(5);
		System.out.println(querySingle);
		System.out.println("------------");*/
		
		//测试多笔查询
		/*List<Employee> queryMore = queryMore();
		for (Employee employee : queryMore) {
			System.out.println(employee);
		}*/
		
		Employee employee = new Employee();
		employee.setId(12);
		employee.setName("yoyo");
		employee.setGender("male");
		employee.setEmail("yoyo@163.com");
		try {
			/*
			 * 开启事务
			 */
			JdbcUtils04.getInstance().beginTransaction();
			insertEmployee(employee);
			
			/*if(true) {
				throw new SQLException();
			}*/
		
			JdbcUtils04.getInstance().commitTransaction();
		} catch(SQLException e) {
   			/*
			 * 回滚事务
			 */
			System.out.println("****************");
			JdbcUtils04.getInstance().rollbackTransaction();
   			throw e;
   		} finally {
   			/*
   			 * 释放连接
   			 */
            JdbcUtils04.getInstance().releaseConnection(); 			
		}
	}
	
	public Employee querySingle(int id) throws SQLException {
		JdbcOperation<Employee> op = new JdbcOperation<>();
    	String sql = "select * from employee where id = ?";
    	Object[] params = {id};
    	RsHandler<Employee> rh = new RsHandler<Employee>() {
			@Override
			public Employee handler(ResultSet rs) throws SQLException {
				Employee emp = new Employee();
				while(rs.next()) {
					emp.setId(rs.getInt("id"));
					emp.setName(rs.getString("name"));
					emp.setEmail(rs.getString("email"));
					emp.setGender(rs.getString("gender"));
				}
				return emp;
			}
    	};
    	return op.querySingleResult(sql, rh, params);
    }
    
   	public List<Employee> queryMore() throws SQLException {
   			JdbcOperation<Employee> op = new JdbcOperation<>();
   	       	String sql = "select * from employee";
   	       	Object[] params = {};
   	       	RsHandler<List<Employee>> rh = new RsHandler<List<Employee>>() {
   	   			@Override
   	   			public List<Employee> handler(ResultSet rs) throws SQLException {
   	   				List<Employee> employee = new ArrayList<>();
   	   				while(rs.next()) {
   	   					Employee emp = new Employee();
   	   					emp.setId(rs.getInt("id"));
   	   					emp.setName(rs.getString("name"));
   	   					emp.setEmail(rs.getString("email"));
   	   					emp.setGender(rs.getString("gender"));
   	   					employee.add(emp);
   	   				}
   	   				return employee;
   	   			}
   	       	};
   	       	return op.queryMoreResult(sql, rh, params);
   	}
   	
   	/*
	 * 测试增加
	 */
	public void insertEmployee(Employee employee) throws SQLException {
   			JdbcOperation<Employee> op = new JdbcOperation<>();
			String sql = "insert into employee values(?,?,?,?)";
			Object[] params = {employee.getId(), employee.getName(), employee.getEmail(), employee.getGender()};
			op.update(sql, params);
	}
}
